﻿using System;
using System.Collections.Generic;
using System.Text;
using RockCompany.Common.Lib;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;
using RockCompany.Common.Constant;
using RockCompany.Master.DTO;
using RockCompany.Master.Constant;

namespace RockCompany.Master.Model
{
    public class SW02FormModel : CommonModel
    {
        public SW02FormModel()
        {
            dbConnector = new DBConnector();
        }

        public DataTable searchWorkOrder(List<string> sWorkOrderList)
        {
            DataTable tempDB = null;
            if (sWorkOrderList.Count > 0)
            {
                string sSql = "SELECT " + CONG_LENH_SX.N_SO_LO + "," + CONG_LENH_SX.N_MA_MAU;
                sSql += " FROM " + CONG_LENH_SX.N_TABLE;
                sSql += " WHERE " + CONG_LENH_SX.N_SO_LO + " IN (";
                foreach (string s in sWorkOrderList)
                {
                    sSql += "'" + s + "',";
                }
                sSql = sSql.Substring(0, sSql.Length - 1) + ")";
                tempDB = selectData(sSql);
            }
            return tempDB;
        }

        public DataTable searchTienDoCL(string sSoLo, string sfromDate, string sToDate)
        {
            DataTable tempDB = null;
            string sSql = "SELECT CONVERT(BIT,'0') AS CHON, NK.*, CL.MA_MAU";
            sSql += " FROM " + NHAT_KY_CL_DTO.N_TABLE + " NK, " + CONG_LENH_SX.N_TABLE + " CL";

            string whereClause = " WHERE NK.SO_LO = CL.SO_LO AND NK.SO_LO LIKE '" + sSoLo + "%'";
            whereClause += " AND CONVERT(VARCHAR,NK." + NHAT_KY_CL_DTO.N_CREATION_TIME + ",112) BETWEEN " + sfromDate + " AND " + sToDate;

            tempDB = selectData(sSql + whereClause);

            return tempDB;
        }


        public bool Upsert(string sUserName, DataTable woList)
        {
            foreach (DataRow curRow in woList.Rows)
            {
                if (curRow[CONG_LENH_SX.N_CHON].Equals(true))
                {
                    switch (curRow.RowState)
                    {
                        case DataRowState.Added:
                            if (!insertNhatKy(sUserName, curRow)) { return false; }
                            break;
                        case DataRowState.Modified:
                            if (!updateNhatKy(sUserName, curRow)) { return false; }
                            break;
                    }
                }
            }
            return true;
        }

        private bool insertNhatKy(string sUserName, DataRow newRow)
        {
            Dictionary<string, object> pv = new Dictionary<string, object>();
            pv.Add(NHAT_KY_CL_DTO.N_SO_LO, newRow.Field<string>(NHAT_KY_CL_DTO.N_SO_LO));
            addDecimalParameters(pv, NHAT_KY_CL_DTO.N_SL_TP, newRow[NHAT_KY_CL_DTO.N_SL_TP]);
            addDecimalParameters(pv, NHAT_KY_CL_DTO.N_SL_BTP, newRow[NHAT_KY_CL_DTO.N_SL_BTP]);
            addDecimalParameters(pv, NHAT_KY_CL_DTO.N_SL_BTP_CON_LAI, newRow[NHAT_KY_CL_DTO.N_SL_BTP_CON_LAI]);
            addDecimalParameters(pv, NHAT_KY_CL_DTO.N_SL_BTP_DIEU_CHINH, newRow[NHAT_KY_CL_DTO.N_SL_BTP_DIEU_CHINH]);

            pv.Add(NHAT_KY_CL_DTO.N_CREATION_PROGRAM_ID, "SW02");
            pv.Add(NHAT_KY_CL_DTO.N_CREATION_TIME, DateTime.Now);
            pv.Add(NHAT_KY_CL_DTO.N_CREATION_USER, sUserName);
            return insertData(NHAT_KY_CL_DTO.N_TABLE, pv);
        }

        private bool updateNhatKy(string sUserName, DataRow curRow)
        {
            string sClause = "";
            sClause += NHAT_KY_CL_DTO.N_SO_LO + " = @" + NHAT_KY_CL_DTO.N_SO_LO;

            Dictionary<string, object> updateValues = new Dictionary<string, object>();
            updateValues.Add(NHAT_KY_CL_DTO.N_SO_LO, curRow.Field<string>(NHAT_KY_CL_DTO.N_SO_LO));
            addDecimalParameters(updateValues, NHAT_KY_CL_DTO.N_SL_TP, curRow[NHAT_KY_CL_DTO.N_SL_TP]);
            addDecimalParameters(updateValues, NHAT_KY_CL_DTO.N_SL_BTP, curRow[NHAT_KY_CL_DTO.N_SL_BTP]);
            addDecimalParameters(updateValues, NHAT_KY_CL_DTO.N_SL_BTP_CON_LAI, curRow[NHAT_KY_CL_DTO.N_SL_BTP_CON_LAI]);
            addDecimalParameters(updateValues, NHAT_KY_CL_DTO.N_SL_BTP_DIEU_CHINH, curRow[NHAT_KY_CL_DTO.N_SL_BTP_DIEU_CHINH]);

            updateValues.Add(NHAT_KY_CL_DTO.N_LAST_UPDATE_TIME, DateTime.Now);
            updateValues.Add(NHAT_KY_CL_DTO.N_LAST_UPDATE_PROGRAM_ID, "SW02");
            updateValues.Add(NHAT_KY_CL_DTO.N_LAST_UPDATE_USER, sUserName);
            return updateData2(NHAT_KY_CL_DTO.N_TABLE, sClause, updateValues);
        }

        public bool removeNhatKy(List<DataRow> removedRows, DataTable dt)
        {
            string sWhereClause = " WHERE ";
            sWhereClause += NHAT_KY_CL_DTO.N_SO_LO + " IN(";

            foreach (DataRow curRow in removedRows)
            {
                if (curRow.RowState != DataRowState.Added)
                {
                    sWhereClause += curRow[NHAT_KY_CL_DTO.N_SO_LO].ToString() + ",";
                }

                // Remove rows in DataTable
                dt.Rows.Remove(curRow);
            }

            sWhereClause = sWhereClause.Substring(0, sWhereClause.Length - 1) + ")";

            return removeRow(NHAT_KY_CL_DTO.N_TABLE, sWhereClause, null);
        }

        public bool export2Excel(string newPath, DataTable woList)
        {
            bool b = false;
            if (!CommonConstantIF.BLANK.Equals(newPath))
            {
                if (CommonModel.existData(woList))
                {
                    ExcelExporter excelExport = new ExcelExporter();
                    excelExport.Trans = this.translator;

                    string fileName = "SW02";

                    if (excelExport.open(fileName))
                    {
                        List<DataColumn> displayCols = new List<DataColumn>();
                        displayCols.Add(woList.Columns[CONG_LENH_SX.N_MA_MAU]);
                        displayCols.Add(woList.Columns[CONG_LENH_SX.N_SO_LO]);
                        displayCols.Add(woList.Columns[NHAT_KY_CL_DTO.N_SL_TP]);
                        displayCols.Add(woList.Columns[NHAT_KY_CL_DTO.N_SL_BTP]);
                        displayCols.Add(woList.Columns[NHAT_KY_CL_DTO.N_SL_BTP_DIEU_CHINH]);
                        displayCols.Add(woList.Columns[NHAT_KY_CL_DTO.N_SL_BTP_CON_LAI]);

                        int beginCol = 1, beginRow = 1;

                        excelExport.inputRangeValues(woList, displayCols, beginCol, beginRow, false);
                        // Print total
                        int totalIndex = beginCol + 1 + woList.Rows.Count;
                        excelExport.inputRange(new string[] { "A" + totalIndex, "E" + totalIndex }, "TOTAL", true);
                        excelExport.inputRange(new string[] { "F" + totalIndex, "F" + totalIndex }, "=SUM(" + "F2:" + "F" + (totalIndex - 1).ToString() + ")", true);


                        b = excelExport.saveExcel(newPath);
                        excelExport.close();
                    }
                }
            }
            return b;
        }
    }
}
